import pymysql
from flask import Flask,jsonify,request,send_file
from flask_cors import CORS
from zhengze import *
import os
import json
#连接数据库
conn=pymysql.connect(host='127.0.0.1',port=3306,user="root",password="123456",db="homework",charset="utf8")
cursor=conn.cursor()
#后端服务启动
app=Flask(__name__)
CORS(app,resources=r'/*')
# 注册功能后端接口
@app.route('/api/register',methods=['POST'])
def register():
    #返回给前端的json字符串
    result={}
    #获取前端的传递信息
    name=request.json["name"]
    account=request.json["account"]
    pwd=request.json["pwd"]
    time=request.json["time"]
    priviledge=prividge(account)
    #学生账号注册
    if(priviledge=="student"):
        cursor.execute(r"select * from stuacc where name=%s or account=%s",(name,account))
        res=cursor.fetchall()
        result["msg"] = "注册成功！"
        result["type"] = "success"
        if res:
            result["msg"] = "该学生或账号已经注册！"
            result["type"] = "error"
        if result["type"]=="success":
            cursor.execute("insert into stuacc(name,account,password) values (%s,%s,%s)", (name, account, pwd))
            insert=cursor.execute("insert into stuinfo(name,homework) values(%s,{}) ",(name))
            if not insert:
                result["msg"] = "系统错误，请联系管理员！"
                result["type"] = "error"
    #教师账号注册
    else:
        cursor.execute("select * from acccheck where name=%s or account=%s",(name,account))
        res=cursor.fetchall()
        result["msg"]="注册成功，请等待管理员审核通过！"
        result["type"]="success"
        if res:
            result["msg"]="您的申请还在处理中，请耐心等候！"
            result["type"]="warning"
        cursor.execute("select * from tchacc where name=%s or account=%s",(name,account))
        res=cursor.fetchall()
        if res:
            result["msg"]="该教师或账号已经注册！"
            result["type"]="error"
        if result["type"]=="success":
            cursor.execute("insert into acccheck(name,account,password,time) values (%s,%s,%s)",(name,account,pwd,time))
            insert=cursor.execute("insert into tchinfo(name) values (%s)",(name))
            if not insert:
                result["msg"] = "系统错误，请联系管理员！"
                result["type"] = "error"
    return jsonify(result)
#登录功能后端接口
@app.route('/api/login',methods=['POST'])
def login():
    #返回给前端json字符串
    result={}
    account=request.json["account"]
    pwd=request.json["pwd"]
    #判断是否为管理员
    if account=="admin":
        if pwd=="123456":
            result["msg"]="登录成功，管理员你好,3秒后为你自动跳转！"
            result["type"]='success'
            result["path"]="/admin"
            result["username"] = "admin"
        else:
            result["msg"]="密码错误!"
            result["type"]="error"
            result["path"]="/"
    else:
        priviledge=prividge(account)
        #判断是否为教师登录
        if priviledge=="teacher":
            cursor.execute("select * from acccheck where account=%s",(account))
            res=cursor.fetchall()
            #审核列表存在
            if res:
                result["msg"] = "对不起，该账号仍在审核中，请耐心等候!"
                result["type"] = "warning"
                result["path"] = "/"
                #审核列表不存在
            else:
                cursor.execute("select * from tchacc where account=%s", (account))
                res = cursor.fetchone()
                #教师账号列表也不存在
                if res is None:
                    result["msg"] = "对不起，该账号尚未注册!"
                    result["type"] = "error"
                    result["path"] = "/"

                else:
                    #密码是否输入正确
                    if pwd==res[3]:
                        result["msg"] = "登录成功，"+res[1]+"教师你好,3秒后为你自动跳转！"
                        result["type"] = "success"
                        result["path"] = "/teacher"
                        result["username"]=res[1]
                    else:
                        result["msg"] = "密码错误!"
                        result["type"] = "error"
                        result["path"] = "/"
        #学生登录
        else:
            cursor.execute("select * from stuacc where account=%s", (account))
            res = cursor.fetchone()
            #学生账号是否存在
            if res is None:
                result["msg"] = "对不起，该账号尚未注册!"
                result["type"] = "error"
                result["path"] = "/"
            #密码是否输入正确
            else:
                if pwd == res[3]:
                    result["msg"] = "登录成功，" + res[1] + "学生你好,3秒后为你自动跳转！"
                    result["type"] = "success"
                    result["path"] = "/student"
                    result["username"] = res[1]
                else:
                    result["msg"] = "密码错误!"
                    result["type"] = "error"
                    result["path"] = "/"
    return jsonify(result)
#公告接口
@app.route('/api/getpub',methods=['GET'])
def getpub():
    result={}
    cursor.execute("select * from pubinfo")
    res=cursor.fetchone()
    result["title"]=res[1]
    result["article"]=res[2]
    result["time"]=res[3]
    result["from"]=res[4]
    return jsonify(result)
########admin界面后端接口################
#教师账号审批接口
@app.route('/api/getaccount',methods=['GET'])
def getaccount():
    result={}
    cursor.execute("select * from acccheck")
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置单页显示的条数
        result["pagesize"]=1
        #设置当前页面为1
        result["currentpage"]=1
        index=0
        tabledata=[]
        for item in res:
            singleitem = {}
            index+=1
            singleitem["id"]=index
            singleitem["name"]=item[1]
            singleitem["account"]=item[2]
            singleitem["time"]=item[4]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#同意教师审批
@app.route('/api/accountaccept',methods=['POST'])
def accept():
    result={}
    name=request.json['name']
    account=request.json["account"]
    #获取账号信息
    cursor.execute("select * from acccheck where name=%s and account=%s",(name,account))
    res=cursor.fetchone()
    name=res[1]
    account=res[2]
    password=res[3]
    #插入到教师账号表里
    insert=cursor.execute("insert into tchacc(name,account,password) values (%s,%s,%s)",(name,account,password))
    cursor.execute("insert into tchinfo(name) values (%s)",(name))
    if insert:
        result["msg"]="操作成功！"
        result["type"]="success"
        #删除审批表的对应数据
        cursor.execute("delete from acccheck where name=%s and account=%s",(name,account))
    else:
        result["msg"]="操作失败，请查看系统原因！"
        result["type"]="error"
    return jsonify(result)
#根据姓名查询教师账号
@app.route('/api/gettchaccbyname',methods=['POST'])
def gettchaccbyname():
    result={}
    name=request.json["name"]
    cursor.execute("select * from acccheck where name=%s",(name))
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置单页显示的条数
        result["pagesize"]=1
        #设置当前页面为1
        result["currentpage"]=1
        index=0
        tabledata=[]
        for item in res:
            singleitem = {}
            index+=1
            singleitem["id"]=index
            singleitem["name"]=item[1]
            singleitem["account"]=item[2]
            singleitem["time"]=item[4]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#拒绝教师账号审批
@app.route('/api/accountreject',methods=["POST"])
def reject():
    result={}
    name=request.json['name']
    account=request.json["account"]
    insert=cursor.execute("delete from acccheck where name=%s and account=%s",(name,account))
    if insert:
        result["msg"]="操作成功！！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请查看系统原因！"
        result["type"]="error"
    return jsonify(result)
#课程申请接口
@app.route('/api/getcourse',methods=["GET"])
def getcourse():
    result={}
    cursor.execute("select * from coursecheck")
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置单页显示的条数
        result["pagesize"]=1
        #设置当前页面为1
        result["currentpage"]=1
        index=0
        tabledata=[]
        for item in res:
            singleitem = {}
            index+=1
            singleitem["id"]=index
            singleitem["courseid"]=item[1]
            singleitem["coursename"]=item[2]
            singleitem["openteacher"]=item[3]
            singleitem["stuapply"]=item[4]
            singleitem["time"]=item[5]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#同意课程申请
@app.route('/api/courseaccept',methods=["POST"])
def courseaccept():
    result={}
    #设置中间变量暂时存储存入数据库的数据
    template={}
    stuname=request.json['stuname']
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    coursestring=courseid+"&"+coursename
    #获取账号信息
    cursor.execute("select * from stuinfo where name=%s",(stuname))
    res=cursor.fetchone()
    courseinfo=res[2]
    hwinfo=res[3]
    #获取选课人数
    cursor.execute("select * from courseinfo where courseid=%s and coursename=%s", (courseid,coursename))
    res=cursor.fetchone()
    people=res[4]
    people+=1
    #将数据库的json字符串转为dict
    hwobject=json.loads(hwinfo)
    coursehomework={}
    cursor.execute("select * from homeworkinfo where courseid=%s and coursename=%s",(courseid,coursename))
    res=cursor.fetchall()
    #若申请的课程原本有作业，则需将先前的作业进行添加
    if len(res):
        for homework in res:
            hwid=homework[1]
            homeworkobject={}
            homeworkobject["ishwsubmit"]="否"
            homeworkobject["mark"]=0
            coursehomework[hwid]=homeworkobject
    hwobject[coursestring]=coursehomework
    #将dict转为json字符串存入数据库
    hwstring=json.dumps(hwobject,ensure_ascii=False)
    #将数据库的课程信息字符串转为课程信息列表
    courselist=courseinfo.split("and")
    courseobject={}
    courseobject["courseid"]=courseid
    courseobject["coursename"]=coursename
    if isinstance(courselist,list):
        #插入课程信息dict
        courselist.append(json.dumps(courseobject,ensure_ascii=False))
        courselist.remove('')
        if len(courselist)==1:
            #将课程信息转为字符串
            coursedata=courselist[0]+"and"
            template["coursedata"]=coursedata
        else:
            coursedata="and".join(courselist)
            template["coursedata"] = coursedata
    #更新到学生账号表里
    insert=cursor.execute("update stuinfo set course=%s,homework=%s where name=%s",(template["coursedata"],hwstring,stuname))
    cursor.execute("update courseinfo set people=%s where courseid=%s and coursename=%s",(people,courseid,coursename))
    cursor.execute("delete from coursecheck where stuname=%s and courseid=%s and coursename=%s",(stuname,courseid,coursename))
    if insert:
        result["msg"]="操作成功！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请查看系统原因！"
        result["type"]="error"
    return jsonify(result)
#拒接课程申请
@app.route('/api/coursereject',methods=["POST"])
def coursereject():
    result={}
    stuname=request.json['stuname']
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    insert=cursor.execute("delete from coursecheck where stuname=%s and courseid=%s and coursename=%s",(stuname,courseid,coursename))
    if insert:
        result["msg"]="操作成功！！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请查看系统原因！"
        result["type"]="error"
    return jsonify(result)
#根据课程名称查询课程申请
@app.route('/api/getcoursebyname',methods=["POST"])
def getcoursebyname():
    result={}
    coursename=request.json["name"]
    cursor.execute("select * from coursecheck where coursename=%s",(coursename))
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置单页显示的条数
        result["pagesize"]=1
        #设置当前页面为1
        result["currentpage"]=1
        index=0
        tabledata=[]
        for item in res:
            singleitem = {}
            index+=1
            singleitem["id"]=index
            singleitem["courseid"]=item[1]
            singleitem["coursename"]=item[2]
            singleitem["openteacher"]=item[3]
            singleitem["stuapply"]=item[4]
            singleitem["time"]=item[5]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#修改公告
@app.route('/api/editpub',methods=["POST"])
def editpub():
    result={}
    title=request.json["title"]
    fromwhere=request.json["from"]
    time=request.json["date1"]
    article=request.json["article"]
    update=cursor.execute("update pubinfo set title=%s,article=%s,time=%s,fromwhere=%s",(title,article,time,fromwhere))
    if update:
        result["msg"]="修改成功！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请查看系统原因"
        result["type"]="error"
    return jsonify(result)
#学生账号获取接口
@app.route('/api/getstuacc',methods=["GET"])
def getstuacc():
    result={}
    cursor.execute("select * from stuacc")
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置单页显示的条数
        result["pagesize"]=1
        #设置当前页面为1
        result["currentpage"]=1
        index=0
        tabledata=[]
        for item in res:
            singleitem = {}
            index+=1
            singleitem["id"]=index
            singleitem["name"]=item[1]
            singleitem["account"]=item[2]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#删除学生账号
@app.route('/api/deletestuacc',methods=["POST"])
def deletestuacc():
    result={}
    name=request.json["name"]
    account=request.json["account"]
    insert=cursor.execute("delete from stuacc where name=%s and account=%s",(name,account))
    cursor.execute("delete from stuinfo where name=%s",(name))
    if insert:
        result["msg"]="操作成功！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请查看系统原因！"
        result["type"]="error"
    return jsonify(result)
#根据姓名查找学生
@app.route('/api/getstubyname',methods=["POST"])
def getstubyname():
    result = {}
    name = request.json["name"]
    cursor.execute("select * from stuacc where name=%s", (name))
    res = cursor.fetchall()
    total = len(res)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        for item in res:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["name"] = item[1]
            singleitem["account"] = item[2]
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    return jsonify(result)
#查看全部课程信息
@app.route('/api/getallcourse',methods=["GET"])
def getallcourse():
    result={}
    cursor.execute("select * from courseinfo")
    res = cursor.fetchall()
    total = len(res)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        for item in res:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["courseid"] = item[1]
            singleitem["coursename"] = item[2]
            singleitem["openteacher"] = item[3]
            singleitem["people"] = item[4]
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    return jsonify(result)
#删除课程信息
@app.route('/api/deletecourse',methods=["POST"])
def deletecourse():
    result={}
    template={}
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    openteacher=request.json["openteacher"]
    #删除课程信息表和课程审批表的课程信息
    cursor.execute("delete from courseinfo where courseid=%s and coursename=%s and teacher=%s",(courseid,coursename,openteacher))
    cursor.execute("delete from coursecheck where courseid=%s and coursename=%s and openteacher=%s",(courseid,coursename,openteacher))
    cursor.execute("select * from tchinfo where name=%s",(openteacher))
    res=cursor.fetchone()
    tchcourselist=res[2].split("and")
    newcourselist=[]
    #若教师课程中出现待删除的课程，则删除
    for item in tchcourselist:
        coursedict=json.loads(item)
        if coursedict["courseid"]==courseid and coursedict["coursename"]==coursename:
            continue
        else:
            newcourselist.append(item)
    if len(newcourselist)==1:
        template["coursestring"]=newcourselist[0]+"and"
    else:
        template["coursestring"]="and".join(newcourselist)
    #更新教师信息
    cursor.execute("update tchinfo set course=%s where name=%s",(template["coursestring"],openteacher))
    #删除该课程的相关作业信息
    cursor.execute("select * from homeworkinfo where courseid=%s and coursename=%s",(courseid,coursename))
    res=cursor.fetchall()
    if len(res):
        cursor.execute("delete from homeworkinfo where courseid=%s and coursename=%s",(courseid,coursename))
    #删除学生课程表的信息
    cursor.execute("select * from stuinfo")
    stulist=cursor.fetchall()
    courseflag=courseid+"&"+coursename
    for item in stulist:
        template={}
        stuhwobject=json.loads(item[3])
        stucourselist=item[2].split("and")
        newstucourselist=[]
        for course in stucourselist:
            if course:
                coursedict=json.loads(course)
                if coursedict["courseid"] == courseid and coursedict["coursename"] == coursename:
                    #设置更新标志，若存在"1"则需要更新课程信息
                    newstucourselist.append("1")
                    continue
                else:
                    newstucourselist.append(course)
        if courseflag in stuhwobject.keys():
            #若作业中存在该课程的键，则删除
            del stuhwobject[courseflag]
        stuhwjson=json.dumps(stuhwobject,ensure_ascii=False)
        if "1" in newstucourselist:
            newstucourselist.remove("1")
            if len(newstucourselist) == 1:
                template["coursestring"] = newstucourselist[0] + "and"
            else:
                template["coursestring"] = "and".join(newstucourselist)
            cursor.execute("update stuinfo set course=%s,homework=%s where name=%s",(template["coursestring"],stuhwjson,item[1]))
    result["msg"]="操作成功！"
    result["type"]="success"
    return jsonify(result)
#根据课程名称查询课程
@app.route('/api/getallcoursebyname',methods=["POST"])
def getallcoursebyname():
    result = {}
    coursename=request.json["coursename"]
    cursor.execute("select * from courseinfo where coursename=%s",(coursename))
    res = cursor.fetchall()
    total = len(res)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        for item in res:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["courseid"] = item[1]
            singleitem["coursename"] = item[2]
            singleitem["openteacher"] = item[3]
            singleitem["people"] = item[4]
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    return jsonify(result)
########student界面后端接口################
#student界面获取课程列表
@app.route('/api/getstucourse',methods=["GET"])
def getstucourse():
    result = {}
    isselectsubmit = {}
    isselect = {}
    username=request.args.get("username")
    cursor.execute("select * from courseinfo")
    rescourse = cursor.fetchall()
    total = len(rescourse)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        #获取学生拥有的课程
        cursor.execute("select * from stuinfo where name=%s", (username))
        res = cursor.fetchone()
        courselist = res[2].split("and")
        courselist.remove("")
        coursestringlist=[]
        for item in courselist:
            course=json.loads(item)
            coursestringlist.append(course["courseid"]+"&"+course["coursename"])
        for item in rescourse:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["courseid"] = item[1]
            singleitem["coursename"] = item[2]
            singleitem["openteacher"] = item[3]
            singleitem["people"] = item[4]
            if item[1]+"&"+item[2] in coursestringlist:
                isselect[item[1]]=True
            else:
                isselect[item[1]]=False
            cursor.execute("select * from coursecheck where courseid=%s and coursename=%s and stuname=%s",(item[1],item[2],username))
            single=cursor.fetchall()
            if len(single):
                isselectsubmit[item[1]]=True
            else:
                isselectsubmit[item[1]]=False
            #判断学生是否已经拥有该课程
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    result["isselect"]=isselect
    result["isselectsubmit"]=isselectsubmit
    return jsonify(result)
#学生提交选课
@app.route('/api/courseselect',methods=["POST"])
def courseselect():
    result={}
    stuname=request.json["username"]
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    time=request.json["time"]
    teacher=request.json["teacher"]
    insert=cursor.execute("insert into coursecheck(courseid,coursename,openteacher,stuname,time) values (%s,%s,%s,%s,%s)",(courseid,coursename,teacher,stuname,time))
    if insert:
        result["msg"]="选课成功，请等待管理员审核！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请查看系统原因！"
        result["type"]="error"
    return jsonify(result)
#学生退选课程
@app.route('/api/courseregret',methods=["POST"])
def courseregret():
    result={}
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    openteacher=request.json["openteacher"]
    stuname=request.json["username"]
    delete=cursor.execute("delete from coursecheck where courseid=%s and coursename=%s and openteacher=%s and stuname=%s",(courseid,coursename,openteacher,stuname))
    if delete:
        result["msg"]="退选成功！"
        result["type"]="success"
    else:
        result["msg"]="操作失败，请联系系统管理员！"
        result["type"]="error"
    return jsonify(result)
#根据课程名称查询课程
@app.route('/api/searchstucourse',methods=["POST"])
def searchstucourse():
    result = {}
    isselectsubmit = {}
    isselect = {}
    coursename=request.json["coursename"]
    username=request.json["username"]
    cursor.execute("select * from courseinfo where coursename=%s",(coursename))
    rescourse = cursor.fetchall()
    total = len(rescourse)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        #获取学生拥有的课程
        cursor.execute("select * from stuinfo where name=%s", (username))
        res = cursor.fetchone()
        courselist = res[2].split("and")
        courselist.remove("")
        coursestringlist=[]
        for item in courselist:
            course=json.loads(item)
            coursestringlist.append(course["courseid"]+"&"+course["coursename"])
        for item in rescourse:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["courseid"] = item[1]
            singleitem["coursename"] = item[2]
            singleitem["openteacher"] = item[3]
            singleitem["people"] = item[4]
            if item[1]+"&"+item[2] in coursestringlist:
                isselect[item[1]]=True
            else:
                isselect[item[1]]=False
            cursor.execute("select * from coursecheck where courseid=%s and coursename=%s and stuname=%s",(item[1],item[2],username))
            single=cursor.fetchall()
            if len(single):
                isselectsubmit[item[1]]=True
            else:
                isselectsubmit[item[1]]=False
            #判断学生是否已经拥有该课程
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    result["isselect"]=isselect
    result["isselectsubmit"]=isselectsubmit
    return jsonify(result)
#获取学生密码
@app.route('/api/getstuaccpwd',methods=["POST"])
def getstuaccpwd():
    result={}
    username=request.json["username"]
    cursor.execute("select * from stuacc where name=%s",(username))
    res=cursor.fetchone()
    result["account"]=res[2]
    result["password"]=res[3]
    return jsonify(result)
#修改学生账号密码
@app.route('/api/editstuacc',methods=["POST"])
def editstuacc():
    result={}
    username=request.json["username"]
    originpwd=request.json["origin"]
    newpwd=request.json["current"]
    update=cursor.execute("update stuacc set password=%s where name=%s and password=%s",(newpwd,username,originpwd))
    if update:
        result["msg"]="修改密码成功！"
        result["type"]="success"
    else:
        result["msg"]="修改密码失败，请联系管理员！"
        result["type"]="error"
    return jsonify(result)
#获取课程作业信息
@app.route('/api/getstuhw',methods=["GET"])
def getstuhw():
    result={}
    username=request.args.get("username")
    cursor.execute("select homework from stuinfo where name=%s",(username))
    homeworkobject=json.loads(cursor.fetchone()[0])
    total=len(homeworkobject.keys())
    if total:
        #设置当前页数为1
        result["currentpage"]=1
        #设置每页显示一条数据
        result["pagesize"]=1
        tabledata=[]
        index=0
        for item in homeworkobject.keys():
            courseid=item.split("&")[0]
            coursename=item.split("&")[1]
            cursor.execute("select * from homeworkinfo where courseid=%s and coursename=%s",(courseid,coursename))
            res=cursor.fetchall()
            for homework in res:
                index+=1
                singleitem={}
                singleitem["id"]=index
                singleitem["courseid"]=homework[6]
                singleitem["coursename"]=homework[7]
                singleitem["hwid"]=homework[1]
                singleitem["posttime"]=homework[4]
                singleitem["finaltime"]=homework[5]
                singleitem["submitnumber"]=homework[8]
                tabledata.append(singleitem)
        result["tabledata"]=tabledata
        result["total"]=index
    else:
        result["tabledata"]=[]
    return jsonify(result)
#获取作业信息
@app.route('/api/getconcretehw',methods=["POST"])
def getconcretehw():
    result={}
    homework={}
    username=request.json["stuname"]
    courseid=request.json["courseid"]
    hwid=request.json["hwid"]
    cursor.execute("select * from homeworkinfo where courseid=%s and hwid=%s",(courseid,hwid))
    res=cursor.fetchone()
    homework["hwtitle"]=res[2]
    homework["posttime"]=res[4]
    homework["finaltime"]=res[5]
    homework["coursename"]=res[7]
    homework["desc"]=res[3]
    coursestring=courseid+"&"+homework["coursename"]
    cursor.execute("select homework from stuinfo where name=%s",(username))
    homeworkobject=json.loads(cursor.fetchone()[0])
    homework["ishwsubmit"]=homeworkobject[coursestring][hwid]["ishwsubmit"]
    result["hwdata"]=homework
    return jsonify(result)
#学生根据课程名获取作业信息
@app.route('/api/searchstucoursehwbyname',methods=["POST"])
def searchstucoursehwbyname():
    result={}
    username=request.json["username"]
    coursenamepost=request.json["coursename"]
    cursor.execute("select homework from stuinfo where name=%s",(username))
    homeworkobject=json.loads(cursor.fetchone()[0])
    total=len(homeworkobject.keys())
    if total:
        #设置当前页数为1
        result["currentpage"]=1
        #设置每页显示一条数据
        result["pagesize"]=1
        tabledata=[]
        index=0
        for item in homeworkobject.keys():
            courseid=item.split("&")[0]
            coursename=item.split("&")[1]
            if coursenamepost==coursename:
                cursor.execute("select * from homeworkinfo where courseid=%s and coursename=%s",(courseid,coursename))
                res=cursor.fetchall()
                for homework in res:
                    index+=1
                    singleitem={}
                    singleitem["id"]=index
                    singleitem["courseid"]=homework[6]
                    singleitem["coursename"]=homework[7]
                    singleitem["hwid"]=homework[1]
                    singleitem["posttime"]=homework[4]
                    singleitem["finaltime"]=homework[5]
                    singleitem["submitnumber"]=homework[8]
                    tabledata.append(singleitem)
                break
        result["tabledata"]=tabledata
        result["total"]=index
    else:
        result["tabledata"]=[]
    return jsonify(result)
##############################################
###############教师界面接口#####################
#获取教师密码
@app.route('/api/gettchaccpwd',methods=["POST"])
def gettchaccpwd():
    result={}
    username=request.json["username"]
    cursor.execute("select * from tchacc where name=%s",(username))
    res=cursor.fetchone()
    result["account"]=res[2]
    result["password"]=res[3]
    return jsonify(result)
#修改教师账号密码
@app.route('/api/edittchacc',methods=["POST"])
def edittchacc():
    result={}
    username=request.json["username"]
    originpwd=request.json["origin"]
    newpwd=request.json["current"]
    update=cursor.execute("update tchacc set password=%s where name=%s and password=%s",(newpwd,username,originpwd))
    if update:
        result["msg"]="修改密码成功！"
        result["type"]="success"
    else:
        result["msg"]="修改密码失败，请联系管理员！"
        result["type"]="error"
    return jsonify(result)
#获取教师界面课程信息
@app.route('/api/gettchcourse',methods=["GET"])
def gettchcourse():
    result = {}
    username=request.args.get("username")
    cursor.execute("select * from courseinfo where teacher=%s",(username))
    res = cursor.fetchall()
    total = len(res)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        for item in res:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["courseid"] = item[1]
            singleitem["coursename"] = item[2]
            singleitem["openteacher"] = item[3]
            singleitem["people"] = item[4]
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    return jsonify(result)
#根据课程名称查询课程信息
@app.route('/api/searchtchcourse',methods=["POST"])
def searchtchcourse():
    result = {}
    result["tabledata"]=[]
    coursename=request.json["coursename"]
    username=request.json["username"]
    cursor.execute("select * from courseinfo where coursename=%s and teacher=%s",(coursename,username))
    res = cursor.fetchall()
    total = len(res)
    if total:
        result["total"] = total
        # 设置单页显示的条数
        result["pagesize"] = 1
        # 设置当前页面为1
        result["currentpage"] = 1
        index = 0
        tabledata = []
        for item in res:
            singleitem = {}
            index += 1
            singleitem["id"] = index
            singleitem["courseid"] = item[1]
            singleitem["coursename"] = item[2]
            singleitem["openteacher"] = item[3]
            singleitem["people"] = item[4]
            tabledata.append(singleitem)
        result["tabledata"] = tabledata
    else:
        result["tabledata"] = []
    return jsonify(result)
#教师创建课程
@app.route('/api/coursecreate',methods=["POST"])
def coursecreate():
    result={}
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    teacher=request.json["teacher"]
    #判断课程id是否重复
    cursor.execute("select * from courseinfo where courseid=%s",(courseid))
    res=cursor.fetchone()
    if res:
        result["msg"]="对不起，当前课程id已被占用，请重新输入！"
        result["type"]="error"
    else:
        insert=cursor.execute("insert into courseinfo(courseid,coursename,teacher) values (%s,%s,%s)",(courseid,coursename,teacher))
        #获取教师课程信息
        cursor.execute("select * from tchinfo where name=%s",(teacher))
        res=cursor.fetchone()
        courseinfo=res[2]
        courselist = courseinfo.split("and")
        courseobject = {}
        courseobject["courseid"] = courseid
        courseobject["coursename"] = coursename
        if isinstance(courselist, list):
            # 插入课程信息dict
            courselist.append(json.dumps(courseobject, ensure_ascii=False))
            courselist.remove('')
            if len(courselist) == 1:
                # 将课程信息转为字符串
                coursedata = courselist[0] + "and"
                cursor.execute("update tchinfo set course=%s where name=%s",(coursedata,teacher))
            else:
                coursedata = "and".join(courselist)
                cursor.execute("update tchinfo set course=%s where name=%s",(coursedata,teacher))
        if insert:
            result["msg"]="操作成功，课程已开设！"
            result["type"]="success"
        else:
            result["msg"]="操作失败，请联系管理员！"
            result["type"]="error"
    return jsonify(result)
#教师获取课程作业信息
@app.route('/api/gethomeworkinfo',methods=["GET"])
def gethomeworkinfo():
    result={}
    username=request.args.get("username")
    cursor.execute("select * from homeworkinfo where teacher=%s",(username))
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置当前页数为1
        result["currentpage"]=1
        #设置每页显示一条数据
        result["pagesize"]=1
        tabledata=[]
        index=0
        for item in res:
            index+=1
            singleitem={}
            singleitem["id"]=index
            singleitem["courseid"]=item[6]
            singleitem["coursename"]=item[7]
            singleitem["hwid"]=item[1]
            singleitem["posttime"]=item[4]
            singleitem["finaltime"]=item[5]
            singleitem["submitnumber"]=item[8]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#教师根据课程名称获取课程作业信息
@app.route('/api/getcoursehwbyname',methods=["POST"])
def getcoursehwbyname():
    result={}
    username=request.json["username"]
    coursename=request.json["coursename"]
    cursor.execute("select * from homeworkinfo where teacher=%s and coursename=%s",(username,coursename))
    res=cursor.fetchall()
    total=len(res)
    if total:
        result["total"]=total
        #设置当前页数为1
        result["currentpage"]=1
        #设置每页显示一条数据
        result["pagesize"]=1
        tabledata=[]
        index=0
        for item in res:
            index+=1
            singleitem={}
            singleitem["id"]=index
            singleitem["courseid"]=item[6]
            singleitem["coursename"]=item[7]
            singleitem["hwid"]=item[1]
            singleitem["posttime"]=item[4]
            singleitem["finaltime"]=item[5]
            singleitem["submitnumber"]=item[8]
            tabledata.append(singleitem)
        result["tabledata"]=tabledata
    else:
        result["tabledata"]=[]
    return jsonify(result)
#获取作业具体信息
@app.route('/api/gethwconcrete',methods=["POST"])
def gethwconcrete():
    result={}
    hwid=request.json["hwid"]
    courseid=request.json["courseid"]
    cursor.execute("select coursename from courseinfo where courseid=%s",(courseid))
    coursename=cursor.fetchone()[0]
    cursor.execute("select * from stuinfo")
    res=cursor.fetchall()
    total=0
    result["pagesize"]=1
    result["currentpage"]=1
    coursestring=courseid+"&"+coursename
    studentlist=[]
    for student in res:
        homeworkobject=json.loads(student[3])
        if coursestring in homeworkobject.keys():
            if hwid in homeworkobject[coursestring].keys():
                total+=1
                singleitem={}
                singleitem["id"]=total
                singleitem["courseid"]=courseid
                singleitem["coursename"]=coursename
                singleitem["hwid"]=hwid
                singleitem["stuname"]=student[1]
                singleitem["ishwsubmit"]=homeworkobject[coursestring][hwid]['ishwsubmit']
                singleitem["mark"]=homeworkobject[coursestring][hwid]['mark']
                studentlist.append(singleitem)
            else:
                continue
    result["total"]=total
    result["tabledata"]=studentlist
    return jsonify(result)
#查询指定学生的作业提交情况
@app.route('/api/searchhwbyname',methods=["POST"])
def searchhwbyname():
    result={}
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    hwid=request.json["hwid"]
    name=request.json["stuname"]
    coursestring=courseid+"&"+coursename
    cursor.execute("select homework from stuinfo where name=%s",(name))
    homeworkobject=json.loads(cursor.fetchone()[0])
    result["total"]=1
    result["pagesize"]=1
    result["currentpage"]=1
    tabledata=[]
    singleitem={}
    singleitem["id"]=1
    singleitem["courseid"]=courseid
    singleitem["coursename"]=coursename
    singleitem["hwid"]=hwid
    singleitem["stuname"]=name
    singleitem["ishwsubmit"]=homeworkobject[coursestring][hwid]["ishwsubmit"]
    singleitem["mark"]=homeworkobject[coursestring][hwid]["mark"]
    tabledata.append(singleitem)
    result["tabledata"]=tabledata
    return jsonify(result)
#学生作业打分
@app.route('/api/editmark',methods=["POST"])
def editmark():
    result={}
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    stuname=request.json["stuname"]
    hwid=request.json["hwid"]
    mark=request.json["mark"]
    coursestring=courseid+"&"+coursename
    cursor.execute("select homework from stuinfo where name=%s",(stuname))
    homeworkobject=json.loads(cursor.fetchone()[0])
    homeworkobject[coursestring][hwid]["mark"]=mark
    homeworkjson=json.dumps(homeworkobject,ensure_ascii=False)
    update=cursor.execute("update stuinfo set homework=%s where name=%s",(homeworkjson,stuname))
    if update:
        result["msg"]="打分成功！"
        result["type"]="success"
    else:
        result["msg"]="打分失败，请联系管理员！"
        result["type"]="error"
    return jsonify(result)
#教师布置作业
@app.route('/api/sethomework',methods=["POST"])
def sethomework():
    result={}
    result["type"]="success"
    courseid=request.json["courseid"]
    coursename=request.json["coursename"]
    username=request.json["username"]
    hwid=request.json["hwid"]
    hwtitle=request.json["hwtitle"]
    desc=request.json["desc"]
    posttime=request.json["posttime"]
    finaltime=request.json["finaltime"]
    coursestring=courseid+"&"+coursename
    cursor.execute("select course from tchinfo where name=%s",(username))
    courselist=cursor.fetchone()[0].split("and")
    for coursejson in courselist:
        courseobject=json.loads(coursejson)
        if courseid==courseobject["courseid"]:
            #若教师的课程ID存在
            if courseobject["coursename"]==coursename:
                break
            else:
                #教师的课程ID与课程名称不对应
                result["msg"]="您输入的课程名称有误，请核对！"
                result["type"]="error"
        else:
            #教师课程ID不存在
            result["msg"]="您输入的课程ID有误，请核对！"
            result["type"]="error"
    if result["type"]=="error":
        return jsonify(result)
    else:
        #判断作业名是否存在
        cursor.execute("select hwid from homeworkinfo where courseid=%s and coursename=%s",(courseid,coursename))
        allhwid=cursor.fetchall()
        for homework in allhwid:
            if hwid in homework:
                result["msg"]="对不起，该作业名已存在！"
                result["type"]="error"
                return jsonify(result)
        cursor.execute("insert into homeworkinfo(hwid,hwtitle,hwdescription,posttime,finaltime,courseid,coursename,teacher) values (%s,%s,%s,%s,%s,%s,%s,%s)",(hwid,hwtitle,desc,posttime,finaltime,courseid,coursename,username))
        cursor.execute("select * from stuinfo")
        studenttuple=cursor.fetchall()
        #更新学生作业信息
        for student in studenttuple:
            homeworkobject=json.loads(student[3])
            if coursestring not in homeworkobject.keys():
                break
            else:
                homeworkinfo={}
                homeworkinfo["ishwsubmit"]="否"
                homeworkinfo["mark"]=0
                homeworkobject[coursestring][hwid]=homeworkinfo
                homeworkjson=json.dumps(homeworkobject,ensure_ascii=False)
                cursor.execute("update stuinfo set homework=%s where name=%s",(homeworkjson,student[1]))
                result["msg"]="布置成功，作业已经发放到学生处！"
                result["type"]="success"
    return jsonify(result)
#教师获取下载作业的作业后缀名
@app.route('/api/getfiletype',methods=["POST"])
def getfiletype():
    result={}
    courseid=request.json["courseid"]
    stuname=request.json["stuname"]
    hwid=request.json["hwid"]
    fileflag=courseid+stuname+hwid
    cursor.execute("select filetype from fileinfo where fileflag=%s",(fileflag))
    filetype=cursor.fetchone()[0]
    result["filetype"]=filetype
    return jsonify(result)
############################
######接受文件和下载文件接口####
#接收文件
@app.route('/api/upload',methods=["POST"])
def fileupload():
    result={}
    extradata=dict(request.form)
    courseid=extradata["courseid"]
    hwid=extradata["hwid"]
    username=extradata["username"]
    cursor.execute("select * from courseinfo where courseid=%s",(courseid))
    res=cursor.fetchone()
    coursename=res[2]
    teachername=res[3]
    extradata["coursename"]=coursename
    extradata["teachername"]=teachername
    requ_data = {
        'file': request.files.get('file'),
        'file_info': extradata
    }
    # 保存文件
    file_content = requ_data['file']
    file_info = requ_data["file_info"]
    filedir = file_info["teachername"] + file_info["courseid"] + file_info["coursename"] + file_info["hwid"]
    filename = file_content.filename
    filetype = filename.split(".")[1]
    fileflag=file_info["courseid"]+file_info["username"]+file_info["hwid"]
    file_name = file_info["username"] + file_info["hwid"] + "." + filetype
    if os.path.exists('F:/' + filedir):
        file_path = 'F:/' + filedir + "/" + file_name
        if os.path.exists(file_path):
            os.unlink(file_path)
            file_content.save(file_path)
            cursor.execute("update fileinfo set filetype=%s where fileflag=%s",(filetype,fileflag))
            result["msg"]='保存文件成功!'
            result["type"]="success"
        else:
            file_content.save(file_path)
            cursor.execute("insert into fileinfo(fileflag,filetype) values (%s,%s)",(fileflag,filetype))
            result["msg"]='保存文件成功!'
            result["type"]="success"
    else:
        os.mkdir('F:/' + filedir)
        file_path = 'F:/' + filedir + "/" + file_name
        file_content.save(file_path)
        cursor.execute("insert into fileinfo(fileflag,filetype) values (%s,%s)", (fileflag, filetype))
        result["msg"] = '保存文件成功!'
        result["type"] = "success"
    if result["type"]=="success":
        cursor.execute("select homework from stuinfo where name=%s",(username))
        res=cursor.fetchone()
        homeworkobject=json.loads(res[0])
        coursestring=courseid+"&"+coursename
        updatehwpeople={
            "flag":0
        }
        if homeworkobject[coursestring][hwid]["ishwsubmit"]=="否":
            homeworkobject[coursestring][hwid]["ishwsubmit"] = "是"
            homeworkjson=json.dumps(homeworkobject,ensure_ascii=False)
            cursor.execute("update stuinfo set homework=%s where name=%s",(homeworkjson,username))
            updatehwpeople["flag"]=1
        if updatehwpeople["flag"]:
            cursor.execute("select submitnumber from homeworkinfo where hwid=%s and courseid=%s and coursename=%s and teacher=%s",(hwid,courseid,coursename,teachername))
            people=cursor.fetchone()[0]
            people+=1
            cursor.execute("update homeworkinfo set submitnumber=%s where hwid=%s and courseid=%s and coursename=%s and teacher=%s",(people,hwid,courseid,coursename,teachername))
    return jsonify(result)
#上传文件给前端
@app.route('/api/download',methods=["POST"])
def filedownload():
    userdata=request.json
    courseid = userdata["courseid"]
    coursename = userdata["coursename"]
    teacher = userdata["teacher"]
    hwid = userdata["hwid"]
    stuname = userdata["stuname"]
    fileflag=courseid+stuname+hwid
    filedir = "F:/" + teacher + courseid + coursename + hwid + "/"
    cursor.execute("select filetype from fileinfo where fileflag=%s",(fileflag))
    filetype=cursor.fetchone()[0]
    file_path = filedir + stuname + hwid + "."+filetype
    if os.path.exists(file_path):
        return send_file(file_path, as_attachment=True)
    else:
        return jsonify({
            'msg': '文件不存在！',
            "type": "error"
        })

if __name__=="__main__":
    app.run(host='0.0.0.0',port=8989)
    cursor.close()
    conn.commit()
    conn.close()
    print("感谢使用，Powered by lzr")